/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.McKit;
import model.StReproducaoMaterial;

/**
 * Document   : StReproducaoMaterialDAO
 * 
 * Created on : 04/03/2013, 16:45:27 
 * 
 * Author     : Fiúza <rbfiuza@gmail.com>
 */ 

public class StReproducaoMaterialDAO {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = null;

    public ArrayList<StReproducaoMaterial> getStReproducaoMaterial() {
        ArrayList<StReproducaoMaterial> stReproducaoMaterialList = new ArrayList<StReproducaoMaterial>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select"
                    + " id"
                    + ",dsc_st_reproducao_material"
                    + ",qnt_alunos"
                    + ",qnt_tutores"
                    + ",qnt_polos"
                    + ",qnt_prof"
                    + ",qnt_exemp_disc"
                    + ",qnt_disciplina"
                    + ",qnt_total"
                    + ",vlr_unit"
                    + ",vlr_final"
                    + ",indicacoes_parametros"
                + " from streproducaomaterial where 1  = ?";

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 1);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                StReproducaoMaterial stReproducaoMaterial = new StReproducaoMaterial();
                stReproducaoMaterial.setId(rs.getInt("id"));
                stReproducaoMaterial.setDscStReproducaoMaterial(rs.getString("dsc_st_reproducao_material"));
                stReproducaoMaterial.setQntAlunos(rs.getInt("qnt_alunos"));
                stReproducaoMaterial.setQntTutores(rs.getInt("qnt_tutores"));
                stReproducaoMaterial.setQntPolos(rs.getInt("qnt_polos"));
                stReproducaoMaterial.setQntProf(rs.getInt("qnt_prof"));
                stReproducaoMaterial.setQntExemplarDisc(rs.getInt("qnt_exemp_disc"));
                stReproducaoMaterial.setQntDisciplinas(rs.getInt("qnt_disciplina"));
                stReproducaoMaterial.setQntTotal(rs.getFloat("qnt_total"));
                stReproducaoMaterial.setVlrUnit(rs.getFloat("vlr_unit"));
                stReproducaoMaterial.setVlrFinal(rs.getFloat("vlr_final"));
                stReproducaoMaterial.setIndicacoesParametros(rs.getString("indicacoes_parametros"));
              
                
                stReproducaoMaterialList.add(stReproducaoMaterial);
            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return stReproducaoMaterialList;
    }

    public String insertStReproducaoMaterial() {
        String userId = null;

        return userId;
    }

    public boolean updateStReproducaoMaterial(String storeId, StReproducaoMaterial stReproducaoMaterial) {
        boolean success = true;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");
            
            sql = "UPDATE streproducaomaterial SET "
                    + " id = ?"
                    + ",dsc_st_reproducao_material = ?"
                    + ",qnt_alunos = ?"
                    + ",qnt_tutores = ?"
                    + ",qnt_polos = ?"
                    + ",qnt_prof = ?"
                    + ",qnt_exemp_disc = ?"
                    + ",qnt_disciplina = ?"
                    + ",qnt_total = ?"
                    + ",vlr_unit = ?"
                    + ",vlr_final = ?"
                    + ",indicacoes_parametros = ?"
                    + " WHERE id = ?";
            
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, stReproducaoMaterial.getId());
            stmt.setString(2, stReproducaoMaterial.getDscStReproducaoMaterial());
            stmt.setInt(3, stReproducaoMaterial.getQntAlunos());
            stmt.setInt(4, stReproducaoMaterial.getQntTutores());
            stmt.setFloat(5, stReproducaoMaterial.getQntPolos());
            stmt.setFloat(6, stReproducaoMaterial.getQntProf());
            stmt.setFloat(7, stReproducaoMaterial.getQntExemplarDisc());
            stmt.setInt(8, stReproducaoMaterial.getQntDisciplinas());
            stmt.setFloat(9, stReproducaoMaterial.getQntTotal());
            stmt.setFloat(10, stReproducaoMaterial.getVlrUnit());
            stmt.setFloat(11, stReproducaoMaterial.getVlrFinal());
            stmt.setString(12, stReproducaoMaterial.getIndicacoesParametros());            
            stmt.setInt(13, stReproducaoMaterial.getId());

            stmt.executeUpdate();

            stmt.close();
            stmt = null;
            conn.close();
            conn = null;

        } catch (Exception e) {

            success = false;
            System.out.println(e);

        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return success;
    }
}